/*:mode pl-sql:
 * $Id: types-time.sql 17 2008-07-07 07:08:03Z Rook.Alex $ 
 */
 
 /*	time domains  */
 drop domain IF EXISTS dyear cascade;
 create domain dyear as smallint
	check (
		value>1111 and value<7777
	); 
drop domain IF EXISTS dmonth cascade;	
 create domain dmonth as smallint
	check (
		value>0 and value<13
	); 
drop domain IF EXISTS dday cascade;
create domain dday as smallint
	check (
		value>0::numeric and value<32::numeric
	); 
drop domain IF EXISTS dhour cascade;
create domain dhour as smallint
	check (
		value>-1::numeric and value<25::numeric
	); 	
drop domain IF EXISTS dminute cascade;
create domain dminute as smallint
	check (
		value>-1::numeric and value<61::numeric
	); 

/********************** time_point  - point of abstract time ***********************/
drop type IF EXISTS time_point cascade;
create type time_point as 
		(y dyear,         --year
		 mo dmonth,   --month
		 d dday,         --day
		 h dhour,       --hour
		 mi dminute   --minmute
		 ); 


create or replace function adopt_time_point(time_point,timestamp) 
	returns time_point as 
$$
begin
	-- year
	if ($1.y is null) or ($1.y=0) then
	  $1.y=date_part('year',$2)::numeric;
	end if;
	--month
	if ($1.mo is null) or ($1.mo=0) then
		$1.mo=date_part('month',$2)::numeric;
	end if;
	--day
	if ($1.d is null) or ($1.d=0) then
		$1.d=date_part('day',$2)::numeric;
	end if;
	--hour
	if ($1.h is null) then
	    $1.h=date_part('hour',$2)::numeric;
	end if;
	--minute
	if ($1.mi is null) then
	 $1.mi=date_part('minute',$2)::numeric;
	end if;
	return $1;
end;
$$ immutable language plpgsql;

	 
create or replace function cast_timestamp2time_point(timestamp) 
		  returns time_point language 'plpgsql' as $$
declare rsl time_point;
begin
   rsl.y=(date_part('year',$1)::numeric);
   rsl.mo=(date_part('month',$1)::numeric);
   rsl.d=(date_part('day',$1)::numeric);
   rsl.h=(date_part('hour',$1)::numeric);
   rsl.mi=(date_part('minute',$1)::numeric);
return rsl;
end;
$$ immutable;

create or replace function cast_timestamptz2time_point(timestamp with time zone) 
		  returns time_point language 'plpgsql' as 
$$
declare rsl time_point;
begin
	rsl=cast_timestamp2time_point($1::timestamp);
	return rsl;
end ;
$$ immutable ;

create or replace function cast_time_point2timestamp(time_point) 
	returns timestamp as 
$$
declare res timestamp;
begin
	$1=adopt_time_point($1,now()::timestamp);
	res=($1.y||'-'||$1.mo||'-'||$1.d||' '||$1.h||':'||$1.mi)::text::timestamp;
	return res;
end;
$$ immutable language plpgsql;

create cast (timestamp as time_point) 
       with function cast_timestamp2time_point(timestamp);
       
create cast (timestamp with time zone as time_point) 
       with function cast_timestamptz2time_point(timestamp with time zone);

create cast (time_point as timestamp)
       with function cast_time_point2timestamp(time_point);
-- select acctstarttime,acctstarttime::timestamp::time_point,a.username from statcollector a;
 
 /* some macros */
 create or replace function now_time_point() returns time_point as
'
	select now()::time_point;
' immutable language sql;

create or replace function null_time_point() returns time_point as
'
	select (null,null,null,null,null)::time_point;
' immutable language sql;

 /*
  * support operator routines
  */
create or replace function op_equals(time_point,time_point) returns boolean as
$$
declare res boolean;
		ts timestamp;
		t1 time_point;
		t2 time_point;
begin
	ts=now()::timestamp;
	t1=adopt_time_point($1,ts);
	t2=adopt_time_point($2,ts);
	if t1.y=t2.y and t1.mo=t2.mo and t1.d=t2.d and t1.h=t2.h and t1.mi=t2.mi then
		res=true;
	else 
	        res=false;
	end if;
	return res;
end;
$$ immutable language plpgsql;

create or replace function op_not_equals(time_point,time_point) returns boolean as
$$
declare res boolean;
begin
      res =not op_equals($1,$2);
      return res;
end;
$$ immutable language plpgsql;

create or replace function op_small(time_point,time_point) returns boolean as
$$
declare res boolean;
	    ts timestamp;
	    t1 time_point;
	    t2 time_point;
begin
	ts=now()::timestamp;
	t1=adopt_time_point($1,ts);
	t2=adopt_time_point($2,ts);
	if t1::timestamp<t2::timestamp then
	  res=true;
         else
	  res=false;
	end if;
	return res;
end;
$$ immutable language plpgsql;

create or replace function op_big(time_point,time_point) returns boolean as
$$
declare res boolean;
begin
     res= not (op_equals($1,$2) or op_small($1,$2));
     return res;
end;
$$ immutable language plpgsql;

create or replace function op_big_equals(time_point,time_point) returns boolean as
$$
 declare res boolean;
begin
	res=not op_small($1,$2);
	return res;
end;
$$ immutable language plpgsql;

create or replace function op_small_equals(time_point,time_point) returns boolean as
$$
declare res boolean;
begin
	res=not op_big($1,$2);
	return res;
end;
$$ immutable language plpgsql;

create or replace function op_minus(time_point,interval) returns time_point as
$$
declare res time_point;
begin
	res=($1::timestamp-$2)::time_point;
	return res;
end;
$$ immutable language plpgsql;

create or replace function op_minus(time_point,time_point) returns interval as
$$
declare res interval;
begin
	res=$1::timestamp-$2::timestamp;
	return res;
end;
$$ immutable language plpgsql;

create or replace function op_plus(time_point,interval) returns time_point as
$$
declare res time_point;
begin
	res=($1::timestamp+$2)::time_point;
	return res;
end;
$$ immutable language plpgsql;

create or replace function op_max(time_point,time_point)  returns time_point as
$$
begin
	if op_big($1,$2) then
		return $1;
	else 
		return $2;
	end if;
end
$$ immutable language plpgsql;

create or replace function op_min(time_point,time_point)  returns time_point as
$$
begin
	if op_small($1,$2) then
		return $1;
	else 
		return $2;
	end if;
end
$$ immutable language plpgsql;


create operator = (procedure =op_equals,leftarg=time_point,rightarg=time_point,commutator= =);
create operator <> (procedure =op_not_equals,leftarg=time_point,rightarg=time_point,commutator= <>);
create operator < (procedure =op_small,leftarg=time_point,rightarg=time_point);
create operator > (procedure =op_big,leftarg=time_point,rightarg=time_point);
create operator >=(procedure =op_big_equals,leftarg=time_point,rightarg=time_point);
create operator <=(procedure =op_small_equals,leftarg=time_point,rightarg=time_point);
create operator - (procedure =op_minus,leftarg=time_point,rightarg=interval);
create operator - (procedure =op_minus,leftarg=time_point,rightarg=time_point);
create operator + (procedure =op_plus,leftarg=time_point,rightarg=interval);

create or replace function op_supp(time_point,time_point) returns integer as
$$
	declare res integer;
begin
	if op_small($1,$2) then
	  res=-1;
	end if;
	if op_equals($1,$2) then
	  res=0;
	 end if;
	if op_big($1,$2) then
	  res=1;
	end if;
	return res;
end;
$$ immutable language plpgsql;

drop aggregate if exists max(time_point);
create aggregate max(time_point) 
				(stype=time_point,
	  			 sfunc=op_max);     

drop aggregate if exists min(time_point);				 
create aggregate min(time_point)
				(stype=time_point,
	  			 sfunc=op_min);     
-------------- warn, on system account ---------------------
 \c - pgsql
 
drop operator class IF EXISTS time_point_ops using btree cascade;
 
create operator class time_point_ops
	default for type time_point using btree as
		operator 1 < ,
		operator 2 <= ,
		operator 3 = ,
		operator 4 >= ,
		operator 5 >,
		function 1 op_supp(time_point,time_point) ;
 \c - radius	
----------------------------------------------------		


/******************time_part - segment of abstract time******************************************/
drop type IF EXISTS  time_part cascade;

create type time_part as (tstart time_point,tend time_point);

create or replace function empty_time_part() returns time_part as
$$
	select (null_time_point(),null_time_point())::time_part;
$$ language sql;

create or replace function length(time_part) returns interval as 
$$
declare res interval;
begin
  if not ($1 is null) then
	res=$1.tend-$1.tstart;
	return res;
   else 
     return null;
  end if;
end;
$$ immutable language plpgsql;

--Allen J.F Operators

create or replace function op_equals(time_part,time_part) returns boolean as
$$
declare 
     res boolean;
begin
		if $1.tstart=$2.tstart and $1.tend=$2.tend then
		 	res =true;
		else
		   res=false;
		end if;
	return res;
end;
$$ immutable language plpgsql;
-- select op_equals(((null,null,10,0,0)::time_point,(null,null,23,0,0)::time_point)::time_part,((null,null,10,0,0)::time_point,(null,null,23,0,0)::time_point)::time_part);

create or replace function op_before(time_part,time_part) returns boolean as
$$
declare res boolean;
begin
	if $1.tend<$2.tstart then
	 res=true;
	else
	 res=false;
	end if;
	return res;
end;
$$ immutable language plpgsql; 
-- select op_before(((null,null,9,0,0)::time_point,(null,null,12,0,0)::time_point)::time_part,((null,null,14,0,0)::time_point,(null,null,23,0,0)::time_point)::time_part);

create or replace function op_meets(time_part,time_part) returns boolean as
$$
declare res boolean;
begin
	if ($1.tstart=$2.tend+'1 minute'::interval) or ($2.tstart=$1.tend+'1 minute'::interval)  then 
	 	res=true;
	else
	 	res=false;
	end if;
	return res;
end;
$$ immutable language plpgsql; 
-- select op_meets(((null,null,9,0,1)::time_point,(null,null,12,0,0)::time_point)::time_part,((null,null,3,0,0)::time_point,(null,null,9,0,0)::time_point)::time_part);
 
create or replace function op_overlaps(time_part,time_part) returns boolean as
$$
declare res boolean;
begin
	if ($1.tstart <$2.tend or $1.tstart= $2.tend) and ($2.tstart<$1.tend or $2.tstart=$1.tend) then
		res=true;
	else
		res=false;
	end if;
	return res;
end;
$$ immutable language plpgsql;
-- select op_overlaps(((null,null,9,0,1)::time_point,(null,null,12,0,0)::time_point)::time_part,((null,null,3,0,0)::time_point,(null,null,10,0,0)::time_point)::time_part);

create or replace function op_during(time_part,time_part) returns boolean as
$$
declare res boolean;
begin
	if ($2.tstart <$1.tstart or $2.tstart= $1.tstart) and ($2.tend>$1.tend or $2.tend=$1.tend) then
		res=true;
	else
		res=false;
	end if;
	return res;
end;
$$ immutable language plpgsql;
-- select op_during(((null,null,9,0,1)::time_point,(null,null,12,0,0)::time_point)::time_part,((null,null,3,0,0)::time_point,(null,null,14,0,0)::time_point)::time_part);

create or replace function op_starts(time_part,time_part) returns boolean as
$$
declare res boolean;
begin
	if ($1.tstart =$2.tstart) and ($1.tend<$2.tend or $1.tend=$2.tend) then
		res=true;
	else
		res=false;
	end if;
	return res;
end;
$$ immutable language plpgsql;
-- select op_starts(((null,null,9,0,0)::time_point,(null,null,12,0,0)::time_point)::time_part,((null,null,9,0,0)::time_point,(null,null,14,0,0)::time_point)::time_part);

create or replace function op_finishes(time_part,time_part) returns boolean as
$$
declare res boolean;
begin
	if ($1.tend=$2.tend) and ($1.tstart >$2.tstart or $1.tstart =$2.tstart)  then
		res=true;
	else
		res=false;
	end if;
	return res;
end;
$$ immutable language plpgsql;
-- select op_finishes(((null,null,10,0,0)::time_point,(null,null,12,0,0)::time_point)::time_part,((null,null,9,0,0)::time_point,(null,null,12,0,0)::time_point)::time_part);

create or replace function op_merges(time_part,time_part) returns boolean as
$$
declare res boolean;
begin
	if (op_meets($1,$2)) or (op_overlaps($1,$2))  then
	   res=true;
	 else 
	   res=false;
	 end if;
	 return res;
end;
$$ immutable language plpgsql;
-- select op_merges(((null,null,9,0,1)::time_point,(null,null,12,0,0)::time_point)::time_part,((null,null,3,0,0)::time_point,(null,null,9,0,0)::time_point)::time_part);

create or replace function op_contains(time_part,time_part) returns boolean as
$$
declare res boolean;
begin
	if op_during($2,$1) then
	   res=true;
	 else 
	   res=false;
	 end if;
	 return res;
end;
$$ immutable language plpgsql;
-- select op_contains(((null,null,3,0,0)::time_point,(null,null,15,0,0)::time_point)::time_part,((null,null,9,0,1)::time_point,(null,null,12,0,0)::time_point)::time_part);


create or replace function op_union(time_part,time_part) returns time_part as
$$
declare 
	res time_part;
begin
	if op_merges($1,$2) then
		  res=(op_min($1.tstart,$2.tstart),op_max($1.tend,$2.tend))::time_part;
		  return res;
	else 
	        res=empty_time_part();
	         return res;
	end if;
end;
$$ immutable language plpgsql;
--select op_union(((null,null,9,0,1)::time_point,(null,null,12,0,0)::time_point)::time_part,((null,null,3,0,0)::time_point,(null,null,9,0,0)::time_point)::time_part);

create or replace function op_intersect(time_part,time_part) returns time_part as
$$
declare res time_part;
begin
    if op_overlaps($1,$2) then
    	res=(op_max($1.tstart,$2.tstart),op_min($1.tend,$2.tend))::time_part;
	return res;
    else 
       res=empty_time_part();
       return res;
    end if;
end;
$$ immutable language plpgsql;
--select op_intersect(((null,null,9,0,1)::time_point,(null,null,12,0,0)::time_point)::time_part,((null,null,3,0,0)::time_point,(null,null,10,0,0)::time_point)::time_part);


 


